package com.anolesoft.yr.mbus.pre.sql;

public class PreInquirySQL {

    //新增询盘记录
    public static final String SAVE_PRE_INQUIRY = "insert into pre_inquiry(objuid,fac_code,pre_promote_uid,inquiry_code,info_source,source_time,into_company,client_region,client_name,contact,contact_person,client_demand,purchase_quantity,handle_person,business_nature,sales_person,reply_time,reply_way,client_type,client_require,client_quality,new_old_client,suggest,this_month_order,first_order_time,no_order_reason,explains,remark,c_user_uid,c_time,del_flag) values(#{objuid},#{facCode},#{prePromoteUid},#{inquiryCode},#{infoSource},#{sourceTime},#{intoCompany},#{clientRegion},#{clientName},#{contact},#{contactPerson},#{clientDemand},#{purchaseQuantity},#{handlePerson},#{businessNature},#{salesPerson},#{replyTime},#{replyWay},#{clientType},#{clientRequire},#{clientQuality},#{newOldClient},#{suggest},#{thisMonthOrder},#{firstOrderTime},#{noOrderReason},#{explains},#{remark},#{ccUserUid},#{ccTime},#{delFlag})";

    //推广记录是否生成询盘记录
    public static final String QUERY_PRE_INQUIRY_BY_PROMOTE_UID = "select count(1) as count from pre_inquiry a where a.pre_promote_uid=#{prePromoteUid}";

    //查询配件信息（临时查询，正常应该在配件接口中）
    public static final String QUERY_PART_MODEL_PART_NAME = "select a.objuid,a.part_name from part_model a where a.del_flag=0 order by a.part_name asc";

    //查询配件询价（临时查询，正常应该在配件询价接口中）
    public static final String QUERY_SUP_BASE_PART_PRICE = "select c.part_name,c.part_model,c.vice_model,b.offer_time,b.offer_price from sup_base_part a inner join sup_base_part_price b on b.sup_base_part_uid=a.objuid inner join part_model c on c.objuid=a.part_model_uid where a.del_flag=0 and b.del_flag=0 and c.del_flag=0 and c.part_model=#{partModel} order by b.offer_time desc ,b.offer_price desc";

    //查询配件报价（临时查询，正常应该在配件报价接口中）
    public static final String QUERY_CRM_BASE_PART_PRICE = "select b.part_name,b.part_model,b.vice_model,a.offer_time,a.offer_price from crm_base_part_price a inner join part_model b on b.objuid=a.part_model_uid where a.del_flag=0 and b.del_flag=0 and b.part_model=#{partModel} order by a.offer_time desc ,a.offer_price desc";

    //查询服务报价（临时查询，正常应该在服务报价接口中）
    public static final String QUERY_CRM_BASE_SERVICE_PRICE = "select b.prod_name,a.offer_time,a.offer_price from crm_base_service_price a inner join prod_production b on b.objuid=a.prod_production_uid where a.del_flag=0 and b.del_flag=0 and b.prod_name=#{prodName} order by a.offer_time desc ,a.offer_price desc";

    //查询询盘记录主键（一次推广可能存在多次询盘）
    public static final String QUERY_PRE_INQUIERY_BY_UID = "select a.objuid from pre_inquiry a where a.del_flag=0 and a.pre_promote_uid=#{prePromoteUid}";

    //根据主键查询询盘记录，用于询盘回访界面的显示（推广主键查询）
    public static final String QUERY_PRE_INQUIERY_BY_UID2 = "select a.objuid,case when a.crm_base_objuid is null then a.client_name else a.crm_name end as client_name,a.contact_person,a.contact,a.client_demand,a.sales_person,a.c_time from (select a.objuid,a.client_name,a.contact_person,a.contact,a.client_demand,b.emp_name as sales_person,a.c_time,c.crm_name,c.objuid as crm_base_objuid from pre_inquiry a left join fac_employee b on b.objuid=a.sales_person left join crm_base c on c.objuid=a.client_name where a.del_flag=0 and a.pre_promote_uid=#{objuid}) a order by a.c_time asc";

    //根据主键查询询盘记录，用于询盘回访界面的显示（拜访主键查询）
    public static final String QUERY_PRE_INQUIERY_BY_UID3 = "select a.objuid,case when a.crm_base_objuid is null then a.client_name else a.crm_name end as client_name,a.contact_person,a.contact,a.client_demand,a.sales_person,a.c_time from (select a.objuid,a.client_name,a.contact_person,a.contact,a.client_demand,b.emp_name as sales_person,a.c_time,c.crm_name,c.objuid as crm_base_objuid from pre_inquiry a left join fac_employee b on b.objuid=a.sales_person left join crm_base c on c.objuid=a.client_name where a.del_flag=0 and a.pre_visit_uid=#{objuid}) a order by a.c_time asc";

    //根据主键查询询盘记录，用于询盘回访界面的显示（询盘主键查询）
    public static final String QUERY_PRE_INQUIERY_BY_UID4 = "select a.objuid,case when a.crm_base_objuid is null then a.client_name else a.crm_name end as client_name,a.contact_person,a.contact,a.client_demand,a.sales_person,a.c_time from (select a.objuid,a.client_name,a.contact_person,a.contact,a.client_demand,b.emp_name as sales_person,a.c_time,c.crm_name,c.objuid as crm_base_objuid from pre_inquiry a left join fac_employee b on b.objuid=a.sales_person left join crm_base c on c.objuid=a.client_name where a.del_flag=0 and a.pre_inquiry_uid=(select a.pre_inquiry_uid from pre_inquiry a where a.objuid=#{objuid})) a order by a.c_time asc;";

    //新增一条客户信息
    public static final String SAVE_CRM_BASE = "insert into crm_base(objuid,fac_code,base_region_uid,crm_code,crm_name,crm_other_name,crm_address,establish_time,crm_website,email,contact_person,contact_number,fax_number,crm_grade,legal_person,company_type,bank,account,tax_number,remark,c_user_uid,c_time,del_flag) values(#{objuid},#{facCode},#{baseRegionUid},#{crmCode},#{crmName},#{crmOtherName},#{crmAddress},#{establishTime},#{crmWebsite},#{email},#{contactPerson},#{contactNumber},#{faxNumber},#{crmGrade},#{legalPerson},#{companyType},#{bank},#{account},#{taxNumber},#{remark},#{ccUserUid},#{ccTime},#{delFlag})";

    //修改一条客户信息
    public static final String UPDATE_CRM_BASE = "update crm_base a set a.fac_code=#{facCode},a.base_region_uid=#{baseRegionUid},a.crm_code=#{crmCode},a.crm_name=#{crmName},a.crm_other_name=#{crmOtherName},a.crm_address=#{crmAddress},a.establish_time=#{establishTime},a.crm_website=#{crmWebsite},a.email=#{email},a.contact_person=#{contactPerson},a.contact_number=#{contactNumber},a.fax_number=#{faxNumber},a.crm_grade=#{crmGrade},a.legal_person=#{legalPerson},a.company_type=#{companyType},a.bank=#{bank},a.account=#{account},a.tax_number=#{taxNumber},a.remark=#{remark},a.m_user_uid=#{mmUserUid},a.m_time=#{mmTime},a.del_flag=#{delFlag} where a.objuid=#{objuid}";

    //修改询盘记录表人员主键
    public static final String UPDATE_PRE_INQUIRY_CRM_BASE = "update pre_inquiry a set a.crm_base_uid=#{crmBaseUid} where a.pre_promote_uid=#{prePromoteUid}";

}
